﻿Imports System.Data
Imports System.Data.SqlClient
Public Class PlacaDAL
#Region "Incluir"

    Public Function Inserir(ByVal Descricao As String, Ano As String, Cor As String, Chassi As String, ByVal CodStatus As Integer, CodCliente As Integer, CodCarro As Integer) As Integer
        Dim cmd = New SqlCommand
        Dim codigo As Integer
        Try

            cmd.CommandText = "insert into Vendas.Ofi_Placa(pla_descricao,pla_ano,pla_cor,pla_chassi,sta_codigo,cli_codigo,crr_codigo,pla_dtcadastro) values (@descricao,@ano,@cor,@chassi,@status,@cliente,@carro,@data)"
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = Descricao
            cmd.Parameters.Add(New SqlParameter("@ano", SqlDbType.VarChar)).Value = Ano
            cmd.Parameters.Add(New SqlParameter("@cor", SqlDbType.VarChar)).Value = Cor
            cmd.Parameters.Add(New SqlParameter("@chassi", SqlDbType.VarChar)).Value = Chassi
            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.Int)).Value = CodStatus
            cmd.Parameters.Add(New SqlParameter("@cliente", SqlDbType.Int)).Value = CodCliente
            cmd.Parameters.Add(New SqlParameter("@carro", SqlDbType.Int)).Value = CodCarro
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            cmd.Parameters.Add(New SqlParameter("@data", SqlDbType.Date)).Value = Format(Date.Now, "yyyy/MM/dd")

            codigo = objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return codigo
    End Function
#End Region

#Region "Alterar"
    Public Function Alterar(Codigo As Integer, ByVal Descricao As String, Ano As String, Cor As String, Chassi As String, ByVal CodStatus As Integer, CodCliente As Integer, CodCarro As Integer) As Integer
        Dim cod As Integer
        Dim cmd = New SqlCommand
        Try

            cmd.CommandText = "update Vendas.Ofi_Placa set pla_descricao=@descricao,pla_ano=@ano,pla_cor=@cor,pla_chassi=@chassi,sta_codigo=@status,cli_codigo=@cliente,crr_codigo=@carro where pla_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = Descricao
            cmd.Parameters.Add(New SqlParameter("@ano", SqlDbType.VarChar)).Value = Ano
            cmd.Parameters.Add(New SqlParameter("@cor", SqlDbType.VarChar)).Value = Cor
            cmd.Parameters.Add(New SqlParameter("@chassi", SqlDbType.VarChar)).Value = Chassi
            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.Int)).Value = CodStatus
            cmd.Parameters.Add(New SqlParameter("@cliente", SqlDbType.Int)).Value = CodCliente
            cmd.Parameters.Add(New SqlParameter("@carro", SqlDbType.Int)).Value = CodCarro
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
            cod = objconexao.executaCmd(cmd)

        Catch ex As Exception
            Throw ex

        Finally
            cmd = Nothing

        End Try
        Return Codigo
    End Function
#End Region

#Region "Deletar"
    Public Function Deletar(ByVal codigo As Integer) As Integer
        Dim cmd = New SqlCommand
        Dim ret As Integer

        Try

            cmd.CommandText = "update Vendas.Ofi_Placa set  sta_codigo=3 where pla_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            ret = objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return ret
    End Function
#End Region

#Region "Buscar"
    Public Function buscar(ByVal Descricao As String, tipo As Integer) As DataTable
        Dim tab = New DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            Select Case tipo
                Case 0
                    cmd.CommandText = "select * from vw_VenPlaca where pla_codigo = @codigo"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(Descricao)
                Case 1
                    cmd.CommandText = "select * from vw_VenPlaca where pla_descricao COLLATE SQL_Latin1_General_CP1_CI_AI  like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case 3
                    cmd.CommandText = "select * from vw_VenPlaca where crr_descricao COLLATE SQL_Latin1_General_CP1_CI_AI  like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case 2
                    cmd.CommandText = "select * from vw_VenPlaca where cli_nome COLLATE SQL_Latin1_General_CP1_CI_AI  like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case 4
                    cmd.CommandText = "select * from vw_VenPlaca where sta_descricao = @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = Descricao
                Case Else
                    cmd.CommandText = "select * from vw_VenPlaca  order by pla_descricao"

            End Select

            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region

#Region "Valida Replica"
    Function ValidaReplica(Codigo As Integer, Descricao As String, Modelo As String) As Boolean
        Dim tab = New DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from vw_VenCarro where crr_descricao=@descricao and crr_modelo=@modelo and crr_codigo<>@codigo and sta_codigo <>3"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = Descricao
            cmd.Parameters.Add(New SqlParameter("@modelo", SqlDbType.VarChar)).Value = Modelo
            tab = objconexao.executaConsulta(cmd)
            If (tab.Rows.Count = 0) Then
                Return True
            Else
                Return False

            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
    End Function
#End Region


End Class
